﻿/*
 * Author: Tomasz Poradowski
 * 2007-03-15
 *
 * Copyright (c) 2007, Poradowski.com
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without modification, are
 * permitted provided that the following conditions are met:
 * - Redistributions of source code must retain the above copyright notice, this list 
 *   of conditions and the following disclaimer.
 * - Redistributions in binary form must reproduce the above copyright notice, this list
 *   of conditions and the following disclaimer in the documentation and/or other materials 
 *   provided with the distribution.
 * - Neither the name of the Poradowski.com nor the names of its contributors may be used to 
 *   endorse or promote products derived from this software without specific prior written 
 *   permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS &AS IS& AND ANY EXPRESS 
 * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
 * AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER 
 * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT 
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * $Id: CSVMerger.cs 5 2007-03-15 17:55:34Z tomasz.poradowski $
 */

using System;
using System.Collections.Generic;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;

namespace CSVMerger {

    public class MergeException : Exception {
    }

	/// <summary>
	/// Class that takes care of configuration reading and processing CSV source
	/// files to make Excel workbooks.
	/// </summary>
	public class CSVMerger {
	    /// <summary>
	    /// Default column separator in CSV file format.
	    /// </summary>
	    public const string DEFAULT_COLUMN_SEPARATOR = ",";

	    //name of config file
	    private string xmlConfigFile;

	    public CSVMerger(string xmlConfigFileName) {
	        this.xmlConfigFile = xmlConfigFileName;
	    }

	    /// <summary>
	    /// Reads config file and merges all given CSV into XLS files.
	    /// </summary>
	    public void merge() {
	        List<MergeWorkbook> workbooks = readXmlConfigWorkbooks();

	        if (workbooks.Count == 0) {
	            Console.Out.WriteLine("Nothing to do.");
	            return;
	        }

	        int i = 1;
	        foreach (MergeWorkbook wbook in workbooks) {
	            Console.WriteLine("Merging workbook {0} into '{1}'", new object[] {i++, wbook.FileName});
	            try {
	                wbook.mergeSources();
	            } catch (MergeException mExc) {
	                Debug.WriteLine(mExc.ToString());
	                Console.Error.WriteLine("*** Error: " + mExc.Message);
	            }
	        }
	    }

	    /// <summary>
	    /// Reads XML config file.
	    /// </summary>
	    /// <returns>workbook list to produce</returns>
	    private List<MergeWorkbook> readXmlConfigWorkbooks() {
	        List<MergeWorkbook> result = new List<MergeWorkbook>();

		    XmlReaderSettings opts = new XmlReaderSettings();
		    opts.IgnoreComments = true;
		    opts.IgnoreProcessingInstructions = true;
		    opts.IgnoreWhitespace = true;

		    using (XmlReader reader = XmlReader.Create(this.xmlConfigFile, opts)) {
		        reader.Read();
		        reader.ReadStartElement("CSVMerger");

		        while (reader.IsStartElement() && (reader.Name == "Workbook")) {
		            reader.Read();
		            reader.ReadStartElement("Output");

		            string outputFile = reader.ReadString();
		            if (String.IsNullOrEmpty(outputFile)) {
		                throw new XmlException("Node Workbook/Output must not be empty.");
		            }

		            string canOverwrite = reader.GetAttribute("overwrite");
		            reader.ReadEndElement(); //Output

		            //default value if ommited
		            if (canOverwrite == null) {
		                canOverwrite = "false";
		            }

		            //workbook to produce
		            MergeWorkbook wbook = new MergeWorkbook(outputFile, (canOverwrite == "true"));

		            reader.ReadStartElement("Worksheets");

		            while (reader.IsStartElement() && (reader.Name == "Worksheet")) {
		                //get "name" atribute and check if it's not empty
		                string sheetName = reader.GetAttribute("name");
    		            if (String.IsNullOrEmpty(sheetName)) {
    		                throw new XmlException("Attribute Workbook/Worksheet[@name] must not be empty.");
    		            }

		                reader.Read();
		                reader.ReadStartElement("Source");

		                string columnSeparator = reader.GetAttribute("separator");
		                string excludeString = reader.GetAttribute("exclude-columns");
		                string sourceFile = reader.ReadString();

		                //parse "exclude-columns" attribute if not empty
		                int[] excludeColumns = null;
		                if (!String.IsNullOrEmpty(excludeString)) {
		                    //comma separated list of column numbers
		                    string[] elems = excludeString.Split(new char[] {','});

		                    if ((elems != null) && (elems.Length > 0)) {
		                        excludeColumns = new int[elems.Length];
		                        for (int i = 0; i < elems.Length; i++) {
		                            try {
		                                //convert to int
		                                excludeColumns[i] = Int32.Parse(elems[i]);
		                            } catch (Exception exc) {
		                                Debug.WriteLine(String.Format("Error converting '{0}' to integer: {1}", new object[] {elems[i], exc.Message}));

		                                //set default value instead
		                                excludeColumns[i] = -1;
		                            }
		                        }
		                    }
		                }

		                //add source CSV to workbook specification
		                wbook.addSheet(new CSVSource(sourceFile, sheetName,
		                                             columnSeparator, excludeColumns));

		                reader.ReadEndElement(); //Source
		                reader.ReadEndElement(); //Worksheet
		            }
		            reader.ReadEndElement(); //Worksheets

		            //add workbook to list
		            result.Add(wbook);

		            reader.ReadEndElement(); //Workbook
		        }
		    }

	        return result;
	    }
	}

	/// <summary>
	/// CSVSource defines properties of a CSV source file.
	/// </summary>
	public class CSVSource {
	    private string fileName;
	    private string sheetName;
	    private string columnSeparator;
	    private int[] columnsToExclude;

	    /// <summary>
	    /// Defines CSV source file <code>fileName</code> with default column
	    /// separator and no excluded columns. It will be stored
	    /// in <code>sheetName</code> worksheet.
	    /// </summary>
	    /// <param name="fileName">CSV file name</param>
	    /// <param name="sheetName">worksheet name</param>
	    public CSVSource(string fileName, string sheetName)
	        : this(fileName, sheetName, null, null) {
	    }

	    /// <summary>
	    /// Defines CSV source file <code>fileName</code> with a column separator
	    /// <code>columnSeparator</code> and no excluded columns. It will be stored
	    /// in <code>sheetName</code> worksheet.
	    /// </summary>
	    /// <param name="fileName">CSV file name</param>
	    /// <param name="sheetName">worksheet name</param>
	    /// <param name="columnSeparator">column separator</param>
	    public CSVSource(string fileName, string sheetName, string columnSeparator)
	        : this(fileName, sheetName, columnSeparator, null) {
	    }

	    /// <summary>
	    /// Defines CSV source file <code>fileName</code> with a column separator
	    /// <code>columnSeparator</code> and a list of excluded column numbers
	    /// <code>excludedColumns</code>. It will be stored
	    /// in <code>sheetName</code> worksheet.
	    /// </summary>
	    /// <param name="fileName">CSV file name</param>
	    /// <param name="sheetName">worksheet name</param>
	    /// <param name="columnSeparator">column separator</param>
	    /// <param name="excludedColumns">numbers of excluded columns, starting from 0</param>
	    public CSVSource(string fileName, string sheetName,
	                     string columnSeparator, int[] excludedColumns) {
	        this.fileName = fileName;
	        this.sheetName = sheetName;
	        this.columnSeparator = String.IsNullOrEmpty(columnSeparator) ? CSVMerger.DEFAULT_COLUMN_SEPARATOR : columnSeparator;
	        this.columnsToExclude = excludedColumns;
	    }

	    public string ColumnSeparator {
	        get { return this.columnSeparator; }
	    }

	    public string FileName {
	        get { return this.fileName; }
	    }

	    public string SheetName {
	        get { return this.sheetName; }
	    }
	}

	/// <summary>
	/// Definition class of result Excel workbook.
	/// </summary>
	public class MergeWorkbook {
	    private string outputFileName;
	    private bool overwrite = false;
	    private List<CSVSource> sheets;

	    /// <summary>
	    /// 
	    /// </summary>
	    /// <param name="outputFile"></param>
	    public MergeWorkbook(string outputFile) : this(outputFile, false) {
	    }

	    /// <summary>
	    /// 
	    /// </summary>
	    /// <param name="outputFile"></param>
	    /// <param name="canOverwrite"></param>
        public MergeWorkbook(string outputFile, bool canOverwrite) {
            this.outputFileName = outputFile;
            this.overwrite = canOverwrite;
            this.sheets = new List<CSVSource>();
        }

	    public string FileName {
	        get { return this.outputFileName; }
	    }

	    public bool CanOverwrite {
	        get { return this.overwrite; }
	    }

	    public void addSheet(CSVSource source) {
	        this.sheets.Add(source);
	    }

	    /// <summary>
	    /// 
	    /// </summary>
	    /// <exception cref="MergeException">thrown if some merging problems occur</exception>
	    public void mergeSources() {
	        if (this.sheets.Count == 0) {
	            Console.Out.WriteLine("No source files given to produce '{0}'", this.FileName);
	        }

	        //process each source file
	        foreach (CSVSource source in this.sheets) {
	            Console.Out.WriteLine("  Adding '{0}' as '{1}'", source.FileName, source.SheetName);
	        }
	    }
    }
}
